#!/usr/bin/env python
# -*- coding: utf-8 -*-
import sys

sys.path.append("/work/opt/analytic/my_python")
from lib.format_time import *
from lib.HandleMysql import *
from lib.handle_mongo import *
from lib.switch_code import *
from lib.load_data import *
import re
import time

reload(sys)
sys.setdefaultencoding('utf-8')


def get_top_info_detail(date):
    conn = set_stats_conn();
    dict_conn = set_dict_conn()
    ib_conn = set_ib_conn()
    ad_conn = set_ads_conn()
    stat_table = "top_info_detail_stats"

    line_list = []
    today = var_date(date, "-", "+", 1)
    dict_ch = {'1000004': 'zp', '1000002': 'fc', '1000009': 'qc', '1000005': 'pw', '1000001': 'hy', '1000003': 'es',
               '1000419': 'jy', '1000006': 'dp', '1000006': 'dp'}
    cont_dict = {}
    clk_dict = {}
    month_str = get_date_info(date, '%Y%m')
    for k in dict_ch:
        user_dict = {}
        tb = "%s_post_user_show_click_%s" % (dict_ch[k], month_str)
        q = "select user_id,sum(show_count) from %s where post_type=1 and stat_date='%s' group by user_id" % (tb, date)
        its = ib_conn.get_results(q)
        for it in its:
            user_id = it[0]
            show_count = it[1]

            user_dict[str(user_id)] = show_count

        cont_dict[dict_ch[k]] = user_dict

        user_clk_dict = {}
        tb = "%s_post_info_%s" % (dict_ch[k], month_str)
        q = "select puser_id,sum(click_count) from %s where product_type='zhiding' and stat_date='%s' group by puser_id" % (
            tb, date)
        its = ib_conn.get_results(q)
        for it in its:
            user_id = it[0]
            click_count = it[1]

            user_clk_dict[str(user_id)] = click_count
        clk_dict[dict_ch[k]] = user_clk_dict

    #return 
    query = "select user_id, city_id,city_name,area_id,area_name,cate1_id,cate1_name,cate2_id,cate2_name ,sum(amount/if(datediff(end_time,start_time)<1,1,(UNIX_TIMESTAMP(end_time)-UNIX_TIMESTAMP(start_time))/(UNIX_TIMESTAMP(if('%s'>end_time,end_time,'%s'))-UNIX_TIMESTAMP(if('%s'<start_time,start_time,'%s'))))),sum(top_hour/if(datediff(end_time,start_time)<1,1,(UNIX_TIMESTAMP(end_time)-UNIX_TIMESTAMP(start_time))/(UNIX_TIMESTAMP(if('%s'>end_time,end_time,'%s'))-UNIX_TIMESTAMP(if('%s'<start_time,start_time,'%s'))))),sum(cash_amount/if(datediff(end_time,start_time)<1,1,(UNIX_TIMESTAMP(end_time)-UNIX_TIMESTAMP(start_time))/(UNIX_TIMESTAMP(if('%s'>end_time,end_time,'%s'))-UNIX_TIMESTAMP(if('%s'<start_time,start_time,'%s'))))) from top_info where date(start_time)<='%s' and date(end_time)>='%s'  group by user_id,city_id,area_id,cate1_id,cate2_id " % (
        today, today, date, date, today, today, date, date, today, today, date, date, date, date)
    items = conn.get_results(query)
    #print query
    #print len(items)
    #return
    for item in items:
        user_id = item[0]
        city_id = item[1]
        city_name = item[2]
        area_id = item[3]
        area_name = item[4]

        cat1 = item[5]
        cat1name = item[6]
        cat2 = item[7]
        cat2name = item[8]
        pay_amount = item[9]
        top_hour = item[10]
        avg_cash_amount = item[11]

        avg_amount = 0.0
        if pay_amount == None:
            pay_amount = 0
        avg_amount = pay_amount

        channel_id = 0
        channel_name = ''
        q = "select channel_id,channel_name from cate_channel where cate_id=%s limit 1" % cat2
        its = dict_conn.get_results(q)
        if len(its) > 0:
            channel_id = its[0][0]
            channel_name = its[0][1]
            #print "1"
        pv = 0
        clicks = 0
        ch = ''
        if dict_ch.has_key(str(channel_id)):
            ch = dict_ch[str(channel_id)]
            #print ch
        if ch != '':
            u_dict = cont_dict[ch]
            if u_dict.has_key(str(user_id)):
                pv = u_dict[str(user_id)]

            c_dict = clk_dict[ch]
            if c_dict.has_key(str(user_id)):
                clicks = c_dict[str(user_id)]

        #print pv    
        #print clicks

        #print "2"
        cpm = 0.0
        ctr = 0.0
        cpc = 0.0
        if pv != 0:
            cpm = 1000 * avg_amount / float(pv)
            ctr = float(clicks) / float(pv)
        if clicks != 0:
            cpc = avg_amount / float(clicks)

        cash_account = 0
        is_bzb = 0
        is_vip = 0
        user_name = ''
        tb_name = "user_account_%s" % get_date_info(date, '%Y%m')
        q = "select cash_account,is_bzb,is_vip,user_name from %s where user_id=%s and stat_date='%s'" % (
            tb_name, user_id, date)
        its = ad_conn.get_results(q)
        if len(its) > 0:
            cash_account = its[0][0]
            is_bzb = its[0][1]
            is_vip = its[0][2]
            user_name = its[0][3]
            #print "3"
        sale_man_id = 0
        sale_man_name = ''
        sale_man_department = ''
        contact_person = ''
        contact_tel = ''
        q = "select sale_man_id,sale_man_name,sale_man_department,contact_person,contact_tel from order_info where user_id=%s order by order_id desc limit 1" % user_id
        its = conn.get_results(q)
        if len(its) > 0:
            sale_man_id = its[0][0]
            sale_man_name = its[0][1]
            sale_man_department = its[0][2]
            contact_person = its[0][3]
            contact_tel = its[0][4]
            #print "4"
        param = [date, user_id, user_name, channel_id, channel_name, city_id, city_name, area_id, area_name, cat1,
                 cat1name, cat2, cat2name, avg_amount, top_hour, pv, clicks, cpm, ctr, cpc, cash_account, is_bzb,
                 is_vip, sale_man_id, sale_man_name, sale_man_department, contact_person, contact_tel, avg_cash_amount]
        #print param
        line_list.append('\t'.join(map(lambda i: str(i), param)))
        #print  param;

    cont = '\n'.join(line_list)
    cont = cont + '\n'
    path = "/work/opt/analytic/lh_tmp_data/topinfo_data/report/"
    file_name = "top_info_detail_stats_%s.txt" % date.replace('-', "")
    writeTextToFile(path, file_name, "w", cont)

    q = "delete from top_info_detail_stats where stat_date='%s'" % date
    conn.execute_sql(q)
    load_data(conn, path + file_name, stat_table, '\t')

    conn.close()
    ad_conn.close()
    dict_conn.close()
    ib_conn.close();


def get_top_info_data(date):
    conn = set_stats_conn();
    dict_conn = set_dict_conn()
    ib_conn = set_ib_conn()
    ad_conn = set_ads_conn()
    stat_table = "top_info_data_stats"

    line_list = []
    today = var_date(date, "-", "+", 1)
    dict_ch = {'1000004': 'zp', '1000002': 'fc', '1000009': 'qc', '1000005': 'pw', '1000001': 'hy', '1000003': 'es',
               '1000419': 'jy', '1000006': 'dp', '1000006': 'dp'}
    show_dict = {}
    clk_dict = {}
    month_str = get_date_info(date, '%Y%m')
    for k in dict_ch:
        s_dict = {}
        tb = "%s_list_analytics_%s" % (dict_ch[k], month_str)
        q = "select cate,area,sum(actionPv) from %s where stat_date='%s' group by cate,area" % (tb, date)
        its = conn.get_results(q)
        for it in its:
            cate = it[0]
            area = it[1]
            pv = it[2]

            key = '%s_%s' % (cate, area)
            s_dict[key] = pv
        show_dict[dict_ch[k]] = s_dict

        c_dict = {}
        tb = "%s_show_info_%s" % (dict_ch[k], month_str)
        q = "select local_id,cate_id,sum(pv) from %s where  stat_date='%s' group by local_id,cate_id " % (tb, date)
        its = ib_conn.get_results(q)
        for it in its:
            local_id = it[0]
            cate_id = it[1]
            pv = it[2]

            key = '%s_%s' % (cate_id, local_id)
            c_dict[key] = pv

        clk_dict[dict_ch[k]] = c_dict

    #return 
    query = "select city_id,city_name,area_id,area_name,cate1_id,cate1_name,cate2_id,cate2_name ,sum(case when date(pay_date)='%s' then amount end),sum(amount/if(datediff(end_time,start_time)<1,1,(UNIX_TIMESTAMP(end_time)-UNIX_TIMESTAMP(start_time))/(UNIX_TIMESTAMP(if('%s'>end_time,end_time,'%s'))-UNIX_TIMESTAMP(if('%s'<start_time,start_time,'%s'))))),sum(top_hour/if(datediff(end_time,start_time)<1,1,(UNIX_TIMESTAMP(end_time)-UNIX_TIMESTAMP(start_time))/(UNIX_TIMESTAMP(if('%s'>end_time,end_time,'%s'))-UNIX_TIMESTAMP(if('%s'<start_time,start_time,'%s'))))),sum(cash_amount/if(datediff(end_time,start_time)<1,1,(UNIX_TIMESTAMP(end_time)-UNIX_TIMESTAMP(start_time))/(UNIX_TIMESTAMP(if('%s'>end_time,end_time,'%s'))-UNIX_TIMESTAMP(if('%s'<start_time,start_time,'%s'))))),sum(case when date(pay_date)='%s' then pay_amount-amount end),count(distinct case when  date(pay_date)='%s' and pay_amount>amount then user_id end),count(1),count(distinct user_id) from top_info where date(start_time)<='%s' and date(end_time)>='%s' group by city_id,area_id,cate1_id,cate2_id" % (
        date, today, today, date, date, today, today, date, date, today, today, date, date, date, date, date, date)
    #print query
    items = conn.get_results(query)
    for item in items:
        #user_id = item[0]
        city_id = item[0]
        city_name = item[1]
        area_id = item[2]
        area_name = item[3]

        cat1 = item[4]
        cat1name = item[5]
        cat2 = item[6]
        cat2name = item[7]
        amount = item[8]
        pay_amount = item[9]
        top_hour = item[10]
        avg_cash_amount = item[11]
        refund_amount = item[12]
        if refund_amount < 0:
            refund_amount = 0

        refund_user_count = item[13]
        top_count = item[14]
        top_user_count = item[15]

        avg_amount = 0.0
        if pay_amount == None:
            pay_amount = 0
        avg_amount = pay_amount

        if avg_amount < 0:
            print city_name, cat1name, cat2name, avg_amount

        channel_id = 0
        channel_name = ''
        q = "select channel_id,channel_name from cate_channel where cate_id=%s limit 1" % cat2
        its = dict_conn.get_results(q)
        if len(its) > 0:
            channel_id = its[0][0]
            channel_name = its[0][1]

        top_pv = 0
        clicks = 0
        vip_user_amount = 0
        vip_user_cash_amount = 0
        vip_user_count = 0
        vip_user_amount_rate = 0.0
        vip_user_rate = 0.0
        q = "select sum(pv),sum(clicks),sum(case when is_vip=1 then avg_amount end),count(case when is_vip=1 then 1 end),sum(case when is_vip=1 then avg_cash_amount end) from top_info_detail_stats where city_id=%s and area_id=%s and cate1_id=%s and cate2_id=%s and stat_date='%s'" % (
            city_id, area_id, cat1, cat2, date)
        its = conn.get_results(q)
        if len(its) > 0:
            top_pv = its[0][0]
            clicks = its[0][1]
            vip_user_amount = its[0][2]
            vip_user_count = its[0][3]
            vip_user_cash_amount = its[0][4]
        if top_pv == None:
            top_pv = 0

        if vip_user_amount == None:
            vip_user_amount = 0

        if avg_amount != 0:
            vip_user_amount_rate = vip_user_amount / avg_amount
        if top_user_count != 0:
            vip_user_rate = float(vip_user_count) / float(top_user_count)

        show_pv = 0.0
        if top_count > 0:
            show_pv = float(top_pv) / float(top_count)

        ch = ''
        if dict_ch.has_key(str(channel_id)):
            ch = dict_ch[str(channel_id)]

        #print "1"
        pv = 0
        total_pv = 0
        if ch != '':
            local_id = area_id
            if local_id == 0:
                local_id = city_id
            disp_local_id = 0
            q = "select displocalid from t_displocal where localid=%s" % local_id
            its = dict_conn.get_results(q)
            if len(its) > 0:
                disp_local_id = its[0][0]
            q = "select dispcategoryid from t_dispcategory where cateid=%s" % cat2
            its = dict_conn.get_results(q)
            s_dict = show_dict[ch]
            for it in its:
                c_id = it[0]
                k = "%s_%s" % (c_id, local_id)
                if s_dict.has_key(k):
                    pv = pv + s_dict[k]

            #total_pv
            c_dict = clk_dict[ch]
            k = "%s_%s" % (cat2, local_id)
            if c_dict.has_key(k):
                total_pv = c_dict[k]

        #sprint pv,total_pv

        if pv == None:
            pv = 0
        if total_pv == None:
            total_pv = 0
        if clicks == None:
            clicks = 0

        #print "2"
        channel_cpm = 0.0
        top_cpm = 0.0
        channel_ctr = 0.0
        top_ctr = 0.0
        cpc = 0.0
        arpu = 0.0
        if pv != 0:
            channel_cpm = 1000 * avg_amount / float(pv)
            channel_ctr = float(clicks) / float(pv)
        if top_pv != 0:
            top_cpm = 1000 * avg_amount / float(top_pv)
            top_ctr = float(clicks) / float(top_pv)
        if clicks != 0:
            cpc = avg_amount / float(clicks)
        if top_user_count != 0:
            arpu = avg_amount / float(top_user_count)

        disp_cat1 = 0
        disp_cat1name = ''
        disp_cat2 = 0
        disp_cat2name = ''
        dc = get_disp_cate(cat2, dict_conn)
        if dc != None:
            if len(dc) > 0:
                disp_cat1 = dc[0][0]
                disp_cat1name = dc[0][1]
            if len(dc) > 1:
                disp_cat2 = dc[1][0]
                disp_cat2name = dc[1][1]
        disp_city_id = 0
        disp_city_name = ''
        disp_area_id = 0
        disp_area_name = ''
        dc = get_disp_local(area_id, dict_conn)
        if dc != None:
            if len(dc) > 0:
                disp_city_id = dc[0][0]
                disp_city_name = dc[0][1]
            if len(dc) > 1:
                disp_area_id = dc[1][0]
                disp_area_name = dc[1][1]

        #print "4"
        param = [date, channel_id, channel_name, city_id, city_name, area_id, area_name, cat1, cat1name, cat2, cat2name,
                 amount, avg_amount, top_hour, refund_amount, refund_user_count, top_count, top_user_count,
                 vip_user_amount, vip_user_count, vip_user_amount_rate, vip_user_rate, pv, top_pv, show_pv, total_pv,
                 clicks, channel_cpm, top_cpm, channel_ctr, top_ctr, cpc, arpu, avg_cash_amount, vip_user_cash_amount,
                 disp_city_id, disp_city_name, disp_area_id, disp_area_name, disp_cat1, disp_cat1name, disp_cat2,
                 disp_cat2name]
        line_list.append('\t'.join(map(lambda i: str(i), param)))
        #print  param;
        #return 

    cont = '\n'.join(line_list)
    cont = cont + '\n'
    path = "/work/opt/analytic/lh_tmp_data/topinfo_data/report/"
    file_name = "top_info_data_stats_%s.txt" % date.replace('-', "")
    writeTextToFile(path, file_name, "w", cont)

    q = "delete from top_info_data_stats where stat_date='%s'" % date
    conn.execute_sql(q)
    load_data(conn, path + file_name, stat_table, '\t')

    conn.close()
    ad_conn.close()
    dict_conn.close()
    ib_conn.close();


def get_disp_cate(cate_id, dict_conn):
    query = "select full_path from cate_to_disp_cate where cate_id =%s" % cate_id
    items = dict_conn.get_results(query)
    fullpath = None
    if len(items) > 0:
        fullpath = items[0][0]

    if fullpath is None:
        return None

    query = "select dispcategoryid, catename from t_dispcategory where dispcategoryid in (%s) order by depth asc" % fullpath
    #print q
    return dict_conn.get_results(query)


def get_disp_local(local_id, dict_conn):
    query = "select full_path from local_to_disp_local where local_id =%s" % local_id
    items = dict_conn.get_results(query)
    fullpath = None
    if len(items) > 0:
        fullpath = items[0][0]

    if fullpath is None:
        return None

    query = "select displocalid, localname from t_displocal where displocalid in (%s) order by depth asc" % fullpath
    #print q
    return dict_conn.get_results(query)


def execute(date):
    #get_top_info_data(date)
    try:
        pass
        get_top_info_detail(date)
    except Exception, e:
        print 'get_top_info_details:', e
    try:
        pass
        get_top_info_data(date)
    except Exception, e:
        print 'get_top_info_data:', e


def main():
    if len(sys.argv) == 1:
        start_date = get_yesterday()
        end_date = start_date
    elif len(sys.argv) == 2:
        start_date = format_date(sys.argv[1])
        end_date = start_date
    elif len(sys.argv) == 3:
        start_date = format_date(sys.argv[1])
        end_date = format_date(sys.argv[2])
    stat_date = start_date
    #conn = set_stats_conn();
    while stat_date >= start_date and stat_date <= end_date:
        print "The program of the date of %s is beginning at %s " % (stat_date, get_now())
        execute(stat_date)

        #path = "/work/opt/analytic/lh_tmp_data/topinfo_data/report/"
        #file_name = "top_info_detail_stats_%s.txt"%stat_date.replace('-',"")

        #load_data(conn,path+file_name,"top_info_detail_stats",'\t') 
        print "The program of the date of %s is end at %s " % (stat_date, get_now())
        stat_date = var_date(stat_date)


if __name__ == '__main__':
    main()

    #print 'aa,aa,aa,'[0:0]
